/**
 * 项目名：化工企业安全生产信息化管理平台
 * 日期：    2020-03-01 22:19:08
 * Copyright (c) 2015- -版权所有
 */

package com.joysuch.wwyt.edu.repository;

import java.util.List;

import javax.persistence.Tuple;

import com.joysuch.wwyt.edu.bean.EduExamUserInfoDto;
import com.joysuch.wwyt.edu.bean.EduRecordExcelDto;
import com.joysuch.wwyt.edu.entity.EduExamRecord;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

/**
 * EduExamRecordDao
 */
public interface EduExamRecordDao extends JpaRepository<EduExamRecord, Long>, QuerydslPredicateExecutor<EduExamRecord> {
    public Page<EduExamRecord> findAll(Specification<EduExamRecord> spec, Pageable pageable);

    public EduExamRecord save(EduExamRecord bean);

    public void delete(EduExamRecord bean);

    EduExamRecord findFirstByUserIdAndExamIdOrderByIdDesc(Long userId, Long examId);

    @Query(value = "select * from wwyt_edu_exam_record record where record.DELETE_FLAG = '0' and record.IS_CHECK=1 and  record.exam_id=?1 and record.USER_ID in (?2)",nativeQuery = true)
    List<EduExamRecord> findByExamIdAndUserIds(Long examId, List<Long> userIds);

    @Query(value = "SELECT " +
            " * " +
            "FROM " +
            "wwyt_edu_exam_record " +
            "WHERE DELETE_FLAG = '0' " +
            "and EXAM_ID = ?2 " +
            "and USER_ID = ?1 " +
            "ORDER BY ID DESC " +
            "LIMIT 1,1 " , nativeQuery = true)
    EduExamRecord findSecondByUserIdAndExamIdOrderByIdDesc(Long userId, Long examId);

    List<EduExamRecord> findByUserId(Long userId);

    int countByUserId(Long userId);

    @Query(value = "select count(*) from (select EXAM_ID, USER_ID, count(*), tenent_id, org_code from wwyt_edu_exam_record  group by EXAM_ID, USER_ID, tenent_id,org_code) t", nativeQuery = true)
    int countByExamIdIn(List<Long> examIds);

    int countByExamIdAndUserId(Long examId,Long userId);

    @Query("select m from EduExamRecord m where examId=?1 and userId in (?2)")
    List<EduExamRecord> findByExamIdAndUserIdIn(Long examId, List<Long> userIds);

    public List<EduExamRecord> findByUserIdAndState(Long userId, Integer state);

    @Query(value = "select count(user_id) as userCount, count(id) " + "as examCount,sum(if(state=1,1,0)) "
            + "as passCount from wwyt_edu_exam_record where delete_flag='0'", nativeQuery = true)
    Tuple findExamCount();

    @Query(value = "SELECT * FROM(select ex.id as examId,ex.name as examName,ex.start_time as examTime, null as needExamCount,"
            + " 0 as actualExamCount from wwyt_edu_exam ex left join  wwyt_edu_exam_user_scope eus on eus.exam_id=ex.id "
            + " where ex.delete_flag='0' and ex.tenent_id=:tenentId and ex.org_code like :orgCode% "
            + " and eus.delete_flag='0' and eus.tenent_id=:tenentId and eus.org_code like :orgCode% "
            + " and ex.STATE=1 and not exists (select er.* from wwyt_edu_exam_record er "
            + " where er.EXAM_ID=ex.id and er.DELETE_FLAG='0' and er.tenent_id=:tenentId and er.org_code like :orgCode%) "
            + " and if(:examName is null,1=1,ex.name like %:examName%) and if(:startTime is null,1=1,"
            + " DATE_FORMAT(ex.start_time, '%Y-%m-%d') =:startTime) GROUP BY eus.EXAM_ID order by ex.start_time desc) t1 "
            + " union SELECT * FROM(select ex.id as examId,ex.name as examName,ex.start_time as examTime, count(eus.obj_id) as needExamCount "
            + ",count(distinct(er.user_id)) as actualExamCount"
            + " from wwyt_edu_exam ex left join wwyt_edu_exam_record er "
            + " on er.exam_id=ex.id inner join wwyt_edu_exam_user_scope eus on eus.exam_id=ex.id "
            + " where ex.delete_flag='0' and ex.tenent_id=:tenentId and ex.org_code like :orgCode% "
            + " and er.delete_flag='0' and er.tenent_id=:tenentId and er.org_code like :orgCode% "
            + " and eus.delete_flag='0' and eus.tenent_id=:tenentId and eus.org_code like :orgCode% "
            + " and if(:examName is null,1=1,ex.name like %:examName%) and if(:startTime is null,1=1,"
            + " DATE_FORMAT(ex.start_time, '%Y-%m-%d') =:startTime) GROUP BY eus.EXAM_ID  order by ex.start_time desc) t2", nativeQuery = true)
    List<Tuple> examPage(String examName, String startTime, Long tenentId, String orgCode);

    @Query(value = "select exam_id, count(distinct(user_id)) as count from wwyt_edu_exam_record where delete_flag='0' and SCORE != '未考' and exam_id in (?1)  group by exam_id", nativeQuery = true)
    List<Tuple> findExamUserCountByGroup(List<Long> examIds);

    // @Query("select new EduExamRecord(er.score as score,bu.realName as userName) from EduExamRecord er "
//    @Query("select new EduExamRecord( er.id, er.examId, er.userId, er.score, er.totalScore, er.passScore , er.submitTime, er.submitTimes,er.state, bu.realName ) from EduExamRecord er "
//            + "left join BaseUser bu on er.userId=bu.id where er.deleteFlag='0' and bu.deleteFlag='0' "
//            + "and er.examId=?1 and er.id in (select max(record.id) from EduExamRecord record where record.examId = ?1 and record.deleteFlag='0' group by record.userId) order by er.id desc")
//    Page<EduExamRecord> pageByExamId(Long examId, Pageable pageable);

    @Query(value = "select ex.code as code,ex.name as examName,ct.name as examType,ex.duration_minutes as examTotalTime,"
            + "tp.total_score as totalScore,tp.pass_score as passScore,tp.question_count as questionCount, "
            + "er.submit_time as examTime,er.score as score, "
            + "case er.state when 0 then '考试不通过' when 1 then '考试通过' else '' end  as examResult "
            + "from wwyt_edu_exam_record er left join wwyt_edu_exam ex on er.exam_id=ex.id inner join "
            + "wwyt_common_type ct on ex.type_id=ct.id inner join wwyt_edu_exam_test_paper etp on etp.exam_id=ex.id "
            + "inner join wwyt_edu_test_paper tp on etp.test_paper_id=tp.id " + "where er.user_id=:userId and "
            + "er.delete_flag='0' and ex.delete_flag='0' and ct.delete_flag='0'and etp.delete_flag='0' and "
            + "tp.delete_flag='0'", nativeQuery = true)
    List<Tuple> userExamRecordPage(Long userId);

    @Query("select userId from EduExamRecord where examId=:examId")
    List<Long> findUserIdByExamId(Long examId);

    @Query(value = "select new com.joysuch.wwyt.edu.bean.EduRecordExcelDto(er.id as id,bu.realName as userName,bu.code as userCode,ex.name as examName,er.score as score, er.totalScore as totalScore,"
            + "er.state as state,ct.name as examType,ex.durationMinutes as examTotalTime,er.submitTime as submitTime," +
            " er.costTime as costTime ,er.submitTimes as submitTimes, er.rightCount as rightCount, er.wrongCount as wrongCount,er.isCheck as isCheck,er.userId as userId) "
            + "from EduExamRecord er left join EduExam ex on ex.id=er.examId inner join CommonType ct "
            + "on ct.id=ex.typeId inner join BaseUser bu on er.userId=bu.id where er.deleteFlag='0' and "
            + "ex.deleteFlag='0' and ct.deleteFlag='0' and bu.deleteFlag='0' and er.examId=:examId")
    List<EduRecordExcelDto> findRecordExcelByExamId(Long examId);

    @Query("select new com.joysuch.wwyt.edu.bean.EduExamUserInfoDto(e.name as examName,ct.name as examType,er.submitTime as "
            + "examTime,concat(er.score,'/',er.totalScore) as scoreInfo,case er.state when 1 then '合格' when 0 then '不合格' "
            + "else '' end as examResult) "
            + "from EduExamRecord er left join EduExam e on er.examId=e.id inner join CommonType ct on ct.id=e.typeId "
            + "where er.deleteFlag='0' and e.deleteFlag='0' and ct.deleteFlag='0' and er.userId=:userId")
    Page<EduExamUserInfoDto> userInfoExam(Long userId, Pageable pageable);

    @Query(value = "select count(*) " +
            "from (select re.STATE,re.TENENT_ID,re.ORG_CODE " +
            "      from (select Max(ID) mid, EXAM_ID, USER_ID,TENENT_ID ,ORG_CODE " +
            "            from wwyt_edu_exam_record " +
            "            where DELETE_FLAG = '0' " +
            "            and EXAM_ID = :examId " +
            "            group by USER_ID,TENENT_ID ,ORG_CODE) temp " +
            "               inner join wwyt_edu_exam_record re " +
            "                          on re.ID = temp.mid) temp1 " +
            "where temp1.STATE = '1' ", nativeQuery = true)
    Integer examPassCount(Long examId);

    @Modifying
    @Query("update EduExamRecord set deleteFlag='1' where id in (?1)")
    public void deleteAllById(List<Long> ids);

    @Modifying
    @Query("update EduExamRecord set deleteFlag='1' where examId=?1 and userId in (?2)")
    public void batchDeleteByExamIdAndUserId(Long examId, List<Long> userIdList);

    @Query(value = " select sum(temp.count) as userCount " +
            " from (select count(distinct USER_ID) as count ,TENENT_ID,ORG_CODE " +
            " from wwyt_edu_exam_record " +
            " where DELETE_FLAG = '0' " +
            " group by EXAM_ID ,TENENT_ID ,ORG_CODE ) temp " , nativeQuery = true)
    Integer findUserCount();

    @Query(value = "select count(*) " +
            "from (select re.STATE, re.TENENT_ID,re.ORG_CODE " +
            "      from (select Max(ID) mid, EXAM_ID, USER_ID ,TENENT_ID ,ORG_CODE" +
            "            from wwyt_edu_exam_record " +
            "            where DELETE_FLAG = '0' " +
            "            group by EXAM_ID, USER_ID,TENENT_ID ,ORG_CODE) temp " +
            "               inner join wwyt_edu_exam_record re " +
            "                          on re.ID = temp.mid) temp1 " +
            "where temp1.STATE = '1' ", nativeQuery = true)
    Integer findPassCount();

    @Query(value = "SELECT * FROM wwyt_edu_exam_record \n" +
            "WHERE IS_CHECK = 1 and DELETE_FLAG = '0'\n" +
            "GROUP BY USER_ID,EXAM_ID", nativeQuery = true)
    List<EduExamRecord> selectToBeMarkedList();

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\twwyt_edu_exam_record \n" +
            "WHERE\n" +
            "\tid IN ( SELECT MAX( id ) FROM wwyt_edu_exam_record WHERE USER_ID = ?1 GROUP BY EXAM_ID )",nativeQuery = true)
    List<EduExamRecord> selectByUserIdLatest(Long userId);


    @Query(value = "SELECT * FROM wwyt_edu_exam_record WHERE DELETE_FLAG='0' and USER_ID = ?1 AND EXAM_ID = ?2 ORDER BY ID DESC LIMIT 1",nativeQuery = true)
    EduExamRecord selectByUserIdAndExamIdLatest(Long userId, Long examId);

    @Query(value = "SELECT * FROM wwyt_edu_exam_record WHERE USER_ID = ?1 AND EXAM_ID = ?2 ORDER BY ID DESC LIMIT 1",nativeQuery = true)
    EduExamRecord selectAllEduExamRecordsByUserIdAndExamIdLatest(Long userId, Long examId);

    @Query(value = "select * from wwyt_edu_exam_record where EXAM_ID = ?1 and DELETE_FLAG='0'",nativeQuery = true)
    List<EduExamRecord> getExamId(Long id);

    @Query("select userId from EduExamRecord where examId=:examId and score <> '未考'")
    List<Long> findUserIdByScore(Long examId);
}
